﻿using System;
using System.Data;
using MySqlConnector;
using System.Data.Common;
using DotNetCommon.Extensions;
using DBUtil.SqlSegment;
using DBUtil.Provider.MySql.SqlSegment;
using DotNetCommon.Data;
using System.Reflection;
using System.Linq;
using DotNetCommon;
using System.Threading.Tasks;
using System.Threading;

namespace DBUtil.Provider.MySql;

/// <summary>
/// MySql操作对象
/// </summary>
public partial class MySqlAccess : DBAccess
{
    /// <inheritdoc />
    protected override DbConnection GetConnectionByConnectString() => new MySqlConnection(DBConn);

    #region 一次最大插入行数
    /// <inheritdoc />
    public override int InsertRowsMaxCountPerBatch => base.InsertRowsMaxCountPerBatch > 0 ? base.InsertRowsMaxCountPerBatch : 5000;
    #endregion

    #region 创建DataAdapter
    /// <summary>
    /// 创建IDataAdapter
    /// </summary>
    /// <param name="cmd">命令对象</param>
    /// <returns></returns>
    protected override DataAdapter CreateAdapter(DbCommand cmd)
    {
        return new MySqlDataAdapter(cmd as MySqlCommand);
    }
    #endregion

    #region CreatePara 创建参数
    /// <summary>
    /// 创建参数
    /// </summary>
    /// <returns></returns>
    public override DbParameter CreatePara()
    {
        return new MySqlParameter();
    }
    #endregion

    #region 初始化
    private static Lazy<string> appName = new(() =>
    {
        var name = $"DBUtil-{Assembly.GetEntryAssembly().GetName().Name}";
        if (name.Length > 100) name = name.Substring(0, 100);
        return name;
    });
    /// <summary>
    /// 初始化
    /// </summary>
    /// <param name="DBConn">连接字符串</param>
    /// <param name="Settings">设置</param>
    public MySqlAccess(string DBConn, DBSetting Settings) : base(EnumDBType.MySql, resetConnectionString(DBConn), Settings)
    {
        InitHooks();
    }

    /// <inheritdoc />
    public override string ParaPrefix => "@";

    private static string resetConnectionString(string connectionString)
    {
        var builder = new MySqlConnectionStringBuilder();
        var defaultAppname = builder.ApplicationName;
        builder.ConnectionString = connectionString;
        if (defaultAppname == builder.ApplicationName)
        {
            builder.ApplicationName = appName.Value;
        }
        builder.AllowUserVariables = true;
        return builder.ConnectionString;
    }
    #endregion

    #region 获取当前db/schema/user的sqlseg
    public override string GetCurrentSchemaSqlSeg() => "database()";
    public override string GetCurrentDataBaseSqlSeg() => "database()";
    public override string GetCurrentUserSqlSeg(bool isPure = false) => isPure ? "reverse(substring(reverse(user()),instr(reverse(user()),'@')+1))" : "user()";
    public override string GetCurrentLoginUserSqlSeg(bool isPure = false) => isPure ? "reverse(substring(reverse(user()),instr(reverse(user()),'@')+1))" : "user()";
    public override string GetCurrentDataBaseVersionSqlSeg() => "version()";
    #endregion

    #region 将任意对象转为可拼接的sql ConvertToSqlSeg
    public override Result<string> ConvertToSqlSeg(object obj, object args = null)
    {
        if (obj is DateTimeOffset dateTimeOffset)
        {
            //mysql 8.0.19及以上才允许insert时加时区 且时间和时区之间不能有空格
            //如：2022-02-11 00:30:01+00:00是正确的，而 2022-02-11 00:30:01 +00:00则会报错
            var seg = dateTimeOffset.ToString("yyyy-MM-dd HH:mm:ss.ffffff").TrimEnd('0').Trim('.');
            if (seg.EndsWith(" 00:00:00")) seg = seg.Substring(0, seg.Length - " 00:00:00".Length);
            return Result.Ok($"'{seg}{dateTimeOffset.ToString("zzz")}'");
        }
        if (obj is DateTime dateTime)
        {
            //mysql 最多支持6位小数 超出的自动截断
            string seg;
            if (args is string s) seg = dateTime.ToString(s);
            else seg = dateTime.ToString("yyyy-MM-dd HH:mm:ss.ffffff");
            seg = seg.TrimEnd('0').TrimEnd('.');
            if (seg.EndsWith(" 00:00:00")) seg = seg.Substring(0, seg.Length - " 00:00:00".Length);
            return Result.Ok($"'{seg}'");
        }
        if (obj is TimeOnly timeOnly) obj = timeOnly.ToTimeSpan();
        if (obj is TimeSpan timeSpan)
        {
            //mysql中使用 time 存储 范围是 '-838:59:59.000000' to '838:59:59.000000'
            //精度最大是小数点6位 超出的自动截断
            //注意: 天数需要加到小时上
            if (timeSpan.Days != 0)
            {
                //c#的 TimeSpan.Parse("12.1:2:3.123456").ToString() => 12.01:02:03.123456
                //而到了 mysql 应该是: 289:02:03.123456
                //MysqlConnector 可以直接读成 TimeSpan
                var str = timeSpan.ToString();
                var idx = str.IndexOf(':');
                return Result.Ok($"'{timeSpan.Days * 24 + timeSpan.Hours}:{str.Substring(idx + 1)}'");
            }
            else return Result.Ok($"'{timeSpan}'");
        }
        var res = base.ConvertToSqlSeg(obj, args);
        if (res.Success) return res;

        //TODO 
        return Result.NotOk($"未能转换 {obj.GetType().FullName} 类型数据到sql!");
    }
    #endregion

    #region GetSqlForPageSize
    /// <summary>获得分页的查询语句
    /// </summary>
    /// <param name="selectSql">查询sql如:select id,name from person where age>10</param>
    /// <param name="orderSql">排序字句如:order by id</param>
    /// <param name="pageSize">页面大小,如:10</param>
    /// <param name="pageIndex">页面索引从1开始,如:1</param>
    /// <returns>返回示例:select id,name from person where age>10 order by id limit 0,10 </returns>
    public override string GetSqlForPageSize(string selectSql, string orderSql, int pageSize, int pageIndex)
    {
        string sql = string.Format("{0} {1} limit {2},{3}", selectSql, orderSql, (pageIndex - 1) * pageSize, pageSize);
        return sql;
    }
    #endregion

    #region Is系列: 判断表/视图/列/存储过程是否存在
    private string IsProcedureExistSql(string procPureName, string schemaPureName = null)
    {
        string sql = $"show PROCEDURE status where name={ProtectStringToSeg(procPureName)} and `type` = 'PROCEDURE'";
        if (schemaPureName.IsNotNullOrWhiteSpace()) sql += $" and Db={ProtectStringToSeg(schemaPureName)}";
        else sql += $" and Db={GetCurrentSchemaSqlSeg()}";
        return sql;
    }
    public override async Task<bool> IsProcedureExistAsync(string procPureName, string schemaPureName = null, CancellationToken cancellationToken = default)
    {
        AssertUtil.NotNullOrWhiteSpace(procPureName);
        var sql = IsProcedureExistSql(procPureName, schemaPureName);
        var r = await SelectScalarAsync<string>(sql, cancellationToken);
        return r.IsNotNullOrWhiteSpace();
    }

    private string IsTriggerExistSql(string triggerPureName, string schemaPureName = null)
    {
        var sql = $"select count(1) from information_schema.TRIGGERS t where t.TRIGGER_NAME={ProtectStringToSeg(triggerPureName)}";
        if (schemaPureName.IsNotNullOrWhiteSpace()) sql += $" and t.TRIGGER_SCHEMA = {ProtectStringToSeg(schemaPureName)}";
        else sql += $" and TRIGGER_SCHEMA={GetCurrentSchemaSqlSeg()}";
        return sql;
    }
    public override async Task<bool> IsTriggerExistAsync(string triggerPureName, string schemaPureName = null, CancellationToken cancellationToken = default)
    {
        AssertUtil.NotNullOrWhiteSpace(triggerPureName);
        var sql = IsTriggerExistSql(triggerPureName, schemaPureName);
        return await SelectScalarAsync<int>(sql, cancellationToken) > 0;
    }
    #endregion

    #region 数据库管理对象 Manage
    private DBManage _dbmanage = null;
    /// <summary>
    /// 数据库管理对象
    /// </summary>
    public override DBManage Manage => _dbmanage ??= new MySqlManage(this);
    #endregion

    #region 运算符
    /// <summary>
    /// 数据库日期时间运算符
    /// </summary>
    protected override DateTimeSqlSegment GetDateTimeSqlSegment() => new MySqlDateTimeSqlSegment(this);
    protected override StringSqlSegment GetStringSqlSegment() => new MySqlStringSqlSegment(this);
    protected override ConvertSqlSegment GetConvertSqlSegment() => new MySqlConvertSqlSegment(this);
    protected override OtherSqlSegment GetOtherSqlSegment() => new MySqlOtherSqlSegment(this);
    #endregion

    #region 使用SqlBulkCopy批量插入数据
    public override void BulkCopy(DataTable dt, string tableName = null, int timeoutSeconds = 60 * 30, int notifyAfter = 0, Func<long, bool> callBack = null)
    {
        if (string.IsNullOrWhiteSpace(tableName)) tableName = dt.TableName;
        if (string.IsNullOrWhiteSpace(tableName)) throw new Exception("必须指定要目的表名!");
        MySqlBulkCopy sbc = null;
        if (IsTransaction) sbc = new MySqlBulkCopy((MySqlConnection)CurrentConnection, (MySqlTransaction)CurrentTransaction);
        else if (IsSession) sbc = new MySqlBulkCopy((MySqlConnection)CurrentConnection);
        else sbc = new MySqlBulkCopy((MySqlConnection)GetNewConnection());

        sbc.BulkCopyTimeout = timeoutSeconds;
        sbc.DestinationTableName = tableName;
        sbc.NotifyAfter = notifyAfter;
        if (callBack != null) sbc.MySqlRowsCopied += (object sender, MySqlRowsCopiedEventArgs e) =>
        {
            e.Abort = callBack(e.RowsCopied);
        };
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            sbc.ColumnMappings.Add(new MySqlBulkCopyColumnMapping(i, dt.Columns[i].ColumnName));
        }
        sbc.WriteToServer(dt);
    }
    #endregion

    #region 获取刚插入的自增id的Sql语句
    /// <inheritdoc />
    public override string GetLastInsertedIdSeg(int len = 1)
    {
        if (len == 1) return $"last_insert_id()";
        return $"last_insert_id()+{len - 1}";
    }
    #endregion

    #region 序列
    public override bool IsSupportSequence() => false;

    public override string NextSequenceValueSeg(string name) => throw new NotSupportedException();
    #endregion

    #region PostDealEntityInfo
    protected override void PostDealEntityInfo(EntityInfo entityInfo)
    {
        //先矫正表名
        var schemaName = entityInfo.TableName.SchemaName.IfNullOrWhiteSpaceUse(entityInfo.TableName.DataBaseName);
        if (schemaName.IsNotNullOrWhiteSpace())
        {
            entityInfo.TableName.SetDataBaseName(schemaName);
            entityInfo.TableName.SetSchemaName(schemaName);
        }

        var cols = entityInfo.Props;
        #region 处理 [MySqlColumn]
        for (int i = 0; i < cols.Count; i++)
        {
            var entityPropertyInfo = cols[i];
            var propAttrs = entityPropertyInfo.PropertyInfo.GetCustomAttributes();
            //[MySqlColumn]
            var colAttr = propAttrs.FirstOrDefault(i => i is MySqlColumnAttribute) as MySqlColumnAttribute;
            if (colAttr != null)
            {
                if (colAttr.Name.IsNotNullOrWhiteSpace())
                {
                    entityPropertyInfo.ColumnNamePure = colAttr.Name;
                }
                entityPropertyInfo.ColumnNameQuoted = AddQuote(entityPropertyInfo.ColumnNamePure);
                if (colAttr.TypeName.IsNotNullOrWhiteSpace())
                {
                    entityPropertyInfo.TypeName = colAttr.TypeName;
                }
                // [Column] 源码中默认就是-1
                entityPropertyInfo.Order = colAttr.Order;

                if (colAttr.TypeName.IsNotNullOrWhiteSpace())
                {
                    entityPropertyInfo.IsDbString = colAttr?.TypeName?.Contains("char", StringComparison.OrdinalIgnoreCase) == true || colAttr?.TypeName?.Contains("text", StringComparison.OrdinalIgnoreCase) == true;
                }
            }
        }
        #endregion
    }
    #endregion

    public override string ConvertJsonVariableToSql(string seg, EnumJsonDataType srcType, EnumJsonAcceptAsType destType)
    {
        if (destType == EnumJsonAcceptAsType.DataTableValue) throw new Exception($"不可以将sql语句转为DataTable中的值!");
        //null
        if (srcType == EnumJsonDataType.Null) return seg;
        else if (srcType == EnumJsonDataType.Number)
        {
            //number
            //return cast(1 as json)
            if (destType == EnumJsonAcceptAsType.Doc) return $"cast({seg} as json)";
            //return 1
            else return seg;
        }
        if (srcType == EnumJsonDataType.String)
        {
            //string
            //return '"tom"'
            if (destType == EnumJsonAcceptAsType.Doc) return $"json_quote({seg})";
            //return 'tom'
            else return seg;
        }
        else if (srcType == EnumJsonDataType.Bool)
        {
            //true/false
            //return cast(true as json)
            if (destType == EnumJsonAcceptAsType.Doc) return $"cast({seg} as json)";
            //return true
            else return seg;
        }
        else
        {
            //arr/obj
            //不用区分
            return seg;
        }
    }

    public override string GetDefaultDbType(Type type)
    {
        var colType = base.GetDefaultDbType(type);
        if (colType.IsNotNullOrEmpty()) return colType;
        if (type.IsNullable()) type = Nullable.GetUnderlyingType(type);
        var code = type.GetTypeCode();
        colType = "json";
        switch (code)
        {
            case TypeCode.SByte:
            case TypeCode.Byte:
            case TypeCode.UInt16:
            case TypeCode.Int16:
            case TypeCode.Int32:
            case TypeCode.UInt32:
                colType = "int";
                break;
            case TypeCode.Int64:
            case TypeCode.UInt64:
                colType = "bigint";
                break;
            case TypeCode.Double:
            case TypeCode.Single:
            case TypeCode.Decimal:
                colType = "decimal(30,15)";
                break;
            case TypeCode.DateTime:
                colType = "datetime(6)";
                break;
            case TypeCode.String:
                colType = "text";
                break;
            default:
                break;
        }
        return colType;
    }

    #region Is系列: 判断 表/视图/列/存储过程/触发器 是否存在
    /// <inheritdoc />
    protected override string IsColumnExistInTableOrViewSql(string tableOrViewPureName, string columnPureName, string schemaPureName = null)
    {
        if (schemaPureName.IsNotNullOrWhiteSpace())
        {
            return
                $"""
                select 1 from information_schema.columns t
                where t.table_schema={ProtectStringToSeg(schemaPureName)}
                    and t.table_name={ProtectStringToSeg(tableOrViewPureName)}
                    and t.column_name={ProtectStringToSeg(columnPureName)}
                limit 1
                """;
        }
        else
        {
            return
                $"""
                select 1 from information_schema.columns t
                where t.table_schema={GetCurrentSchemaSqlSeg()}
                    and t.table_name={ProtectStringToSeg(tableOrViewPureName)}
                    and t.column_name={ProtectStringToSeg(columnPureName)}
                limit 1
                """;
        }
    }
    /// <inheritdoc />
    protected override string IsTableOrViewExistSql(string tableOrViewPureName, string schemaPureName = null)
    {
        if (schemaPureName.IsNotNullOrWhiteSpace())
        {
            return
                $"""
                select 1 from information_schema.tables t
                where t.table_schema={ProtectStringToSeg(schemaPureName)}
                    and t.table_name={ProtectStringToSeg(tableOrViewPureName)}
                limit 1
                """;
        }
        else
        {
            return
                $"""
                select 1 from information_schema.columns t
                where t.table_schema={GetCurrentSchemaSqlSeg()}
                    and t.table_name={ProtectStringToSeg(tableOrViewPureName)}
                limit 1
                """;
        }
    }
    #endregion
}